This section introduces the concept of LearningStore schema, and describes the schema used by SharePoint Learning Kit and the underlying Microsoft Learning Components.
The SLK Queries and Query Sets section introduced the concept of LearningStore view—a "virtual table" of data with built-in security rules. LearningStore views are part of a larger concept, LearningStore schema, which is a definition of the kind of data that can be managed by Microsoft Learning Components for an e-learning application such as SLK.
In addition to views, a LearningStore schema can contain LearningStore item types, which correspond directly to tables in the database—"real" tables, as opposed to views which are "virtual" tables.
MLC defines a base schema, containing basic item types and views required for executing e-learning content. The base schema is defined by an XML file, BaseSchema.xml. Applications based on MLC have the option of extending the base schema using another XML file which can extend item types, add new item types, and add new views, among other things. SLK, which is an application based on MLC, extends the base schema to add concepts such as "assignment" and "learner assignment". SLK's extensions to the MLC base schema are defined in SlkSchema.xml.
Item types and views can define database operation-level security rules, implemented as SQL expressions which evaluate to 1 if the operation is allowed to execute and 0 if it isn't. (Items support the operations "query", "add", "delete", and "update"; views only support "query".) Since views are implemented as SQL queries, views can also define row-level security rules that determine which rows get returned, based in part on the role(s) of the current user.
Note The term "row-level security" is sometimes used in other technologies to refer to a broader authorization concept. In LearningStore, "row-level security" simply means a set of conditions that are added to the SQL query of a view—in effect, reducing the set of rows returned from a query to those that the current user is permitted to see. Note that LearningStore also provides mechanisms for securing adds, updates, and deletes of rows, but those fall under the category of "operation-level security". Also, note that operation-level security rules typically make use of information within rows of the table—unless the rule is simply "grant access to all users" or "grant access to no users".
Consider the following examples of a SLK item type compared to a SLK view.
InstructorAssignmentItem** is a LearningStore item type—in other words, there is a table in the SLK database named "InstructorAssignmentItem". This table contains one row for each instructor on each assignment, so if you have 10 assignments with two instructors on each assignment you'll have 20 rows in the InstructorAssignmentItem table.
The security rules for this item type—and for all item types in SLK, in fact—specify that no user can access items of this type. That may seem counterproductive, but the reason is that SLK has chosen to define security rules on views instead of on item types. (Each MLC-based application can decide whether to apply security rules to item types, views, or both. The MLC base schema takes a "secure by default" approach, choosing to disallow access by default. MLC-based applications can override those rules.) Note that an application can turn off security checking for a block of code using LearningStorePrivilegedScope.
InstructorAssignmentListForInstructors is a LearningStore view. Like InstructorAssignmentItem above, this view contains one row for each instructor on each assignment—except that you'll only get rows corresponding to assignments for which the current user is an instructor. (By the way, that last part, "for which the current user is an instructor", is the reason for the "ForInstructors" part of the view name.)
In addition to the row-level security rule described above, InstructorAssignmentListForInstructors specifies the following operation-level security rule: any user can access this view. This view doesn't need more restrictive operation-level security rules because the row-level security rule already sufficiently restricts the query results. For example, consider what happens if the current user is a learner. In that case, the row-level security rule above will cause InstructorAssignmentListForInstructors to return zero rows, indicating that that user is an instructor on zero assignments, which is correct. (Note that a user can be both an instructor and a learner on a given assignment. This view relates to a user's role as an instructor.)
**By the way, If you clicked on links above, such as InstructorAssignmentItem and InstructorAssignmentListForInstructors, you may notice that there are classes, accessible from C# or other programming languages, corresponding to LearningStore item types and views. These classes are generated automatically by the MLC schema compiler (the tool that converts schema XML such as SlkSchema.xml, into .sql and .cs files), and they exist primarily for programming convenience. For example, rather than referring to a column named "AssignmentStartDate", you can use the string constant InstructorAssignmentListForInstructors.AssignmentStartDate, which is longer but is type-checked by the compiler, so you'll catch typing mistakes at compile time. The documentation for these classes also provides further information about these item types and views.
In addition to helper classes such as InstructorAssignmentItem, the schema compiler generates a LearningStore item identifier class for each item type. For example, the item identifier class corresponding to InstructorAssignmentItem is InstructorAssignmentItemIdentifier. This is a class which represents, in a type-safe way, a numeric identifier for a row in the InstructorAssignmentItem table.
Both item types and views can be queried. You can use an item type name (e.g. "UserItem", or, for better compile-time type checking, UserItem.ItemTypeName) in a query displayed in Assignment List Web Part or in a LearningStore query, just like you can use a view name (e.g. "UserWebList" or UserWebList.ViewName)—provided that sufficient operation-level security permissions have been granted (see example).
The next few subsections below describe the MLC and SLK schemas. The last subsection below explains how to customize SLK views and create new views, which allow you to add to the functionality of the SLK Assignment List Web Part and to perform additional querying operations within applications that access SLK programmatically.
Note The Schema column below indicates which schema (MLC base schema or SLK extensions to MLC) define the item type or view. When the Schema column contains MLC+, that's an indication of an item type which is defined in the MLC base schema and extended (with additional columns, in this case) in the SLK schema.
Note The Access Granted To column below defines the conditions under which the current user (i.e. the browser user logged into SharePoint) can access the item type or view using Assignment List Web Part or LearningStore queries. When applications access these items and views using the MLC API or SLK API, or using LearningStorePrivilegedScope, different rules sometimes apply (as described in the API documentation, e.g. SlkStore). For example, by default the current user cannot access the AttemptItem table, but of course MLC can access the AttemptItem table on the user's behalf when MLC executes e-learning content. Also, note that Access Granted To column specifies operation-level security rules, but views may specify additional row-level security rules. For example, even if "Access Granted To" specifies "All users", for views it's possible that the row-level security rules mean you'll get zero rows returned from a query.
| Name | Type | Schema | Each Row Contains | Access Granted To |
| UserItem | Item | MLC+ | Information about one user, including a numeric identifier (UserItemIdentifier) used elsewhere in the database to refer to the user. UserItem items contain a Key property (database column) which, for SLK, contains either the security ID (SID) of the user or the user's login name. (The login name is used for authentication mechanisms such as forms-based authentication which don't use security IDs.) | MLC: no users; SLK: query access for all users. |
| UserWebListItem | Item | SLK | One entry in one user's user Web list, for one site collection. A user Web list is the list of Web sites that appears on the E-Learning Actions page for all document libraries within a given site collection. Each user has at most one user Web list for each site collection (SPSite). | No users. |
| UserWebList | View | SLK | One entry in one user's user Web list, for one site collection. Like UserWebListItem, but the UserWebList view is restricted to user Web lists owened by the current user. | All users. |
| LearnerGlobalObjectiveItem | Item | MLC | Information about the progress of one learner against a particular global objective, across all activities in all e-learning packages. These objectives may be global to a single package or global to the system—the data in this table does not distinguish between these two cases. | No users. |
These item types (tables) contain information about e-learning packages. This information is not specific to any assignment or attempt on the package. These tables are not involved in assignments of non-e-learning documents.
| Name | Type | Schema | Each Row Contains | Access Granted To |
| PackageItem | Item | MLC+ | Information about one e-learning package. Before a package can be attempted, a row referring to it must be added to this table. When that happens, detailed information about the package is added to other tables in the schema; for example, information about each activity is added to the ActivityPackageItem table. | No users. |
| ActivityPackageItem | Item | MLC | Information about one activity within one e-learning package. | No users. |
| ResourceItem | Item | MLC | Information about resources used in activities, including the XML of each <resource> element in the package. | No users. |
| ActivityObjectiveItem | Item | MLC | Information about one local objective of one activity within one e-learning package. Objectives that are added to a package by a SCO via the RTE are not included in this table. | No users. |
| CommentFromLmsItem | Item | MLC | A "comments from LMS" string related to a specific activity within an e-learning package. These are read-only by SCOs. SLK does not add rows to the CommentsFromLmsItem table. | No users. |
| GlobalObjectiveItem | Item | MLC | Information about one global objective related to one organization of one e-learning package, or one global objective that's not constrained to any one organization of any package. | No users. |
| MapActivityObjectiveToGlobalObjectiveItem | Item | MLC | A mapping between an ActivityObjectiveItem and a GlobalObjectiveItem. | No users. |
SLK's LearningStore schema adds the concept of assignments to MLC's base schema. The following item types and views help implement this and related concepts.
| Name | Type | Schema | Each Row Contains | Access Granted To |
| AssignmentItem | Item | SLK | Information about one assignment. | No users. |
| AssignmentPropertiesView | View | SLK | Information about one assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns information about a single assignment, specified as a view parameter. | Instructors and learners of the assignment. |
| AssignmentListForInstructors | View | SLK | Information about one assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each assignment for which the current user is an instructor. | All users. |
| InstructorAssignmentItem | Item | SLK | Information about one instructor assignment, i.e. the mapping of one instructor to one assignment. For example, if an assignment has three instructors, there will be three InstructorAssignmentItem table rows associated with that assignment. | No users. |
| InstructorAssignmentListForInstructors | View | SLK | Information about one instructor assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each instructor on each assignment for which the current user is an instructor. | All users. |
| LearnerAssignmentItem | Item | SLK | Information about one learner assignment, i.e. the mapping of one learner to one assignment. For example, if an assignment has 30 learners, there will be 30 LearnerAssignmentItem table rows associated with that assignment. | No users. |
| LearnerAssignmentListForInstructors | View | SLK | Information about one learner assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each learner on each assignment for which the current user is an instructor. | All users. |
| LearnerAssignmentListForLearners | View | SLK | Information about one learner assignment, as well as information about the e-learning package (if any) associated with the assignment. This view returns one row for each assignment for which the current user is a learner. | All users. |
| LearnerAssignmentView | View | SLK | Specific information about a learner assignment. | No users. |
The following item types help implement attempts on e-learning packages. These tables are not involved in assignments of non-e-learning documents.
| Name | Type | Schema | Each Row Contains | Access Granted To |
| AttemptItem | Item | MLC+ | Information about one attempt, i.e. one instance of one learner executing one organization of one e-learning package. If the same learner attempts the same organization twice, two AttemptItem items (table rows) are created. | No users. |
| PackageGlobalObjectiveItem | Item | MLC | Information about progress on one global objective related to one attempt. | No users. |
| ActivityAttemptItem | Item | MLC | Information about one activity in the context of one attempt. | No users. |
| AttemptObjectiveItem | Item | MLC | Information about the learner's progress on one local objective for one activity of one attempt. | No users. |
| CommentFromLearnerItem | Item | MLC | One comment from the learner related to one activity of one attempt. | No users. |
| SequencingLogEntryItem | Item | MLC | One sequencing log entry, related to one activity of one attempt, or related to an entire attempt. See StoredLearningSession.LoggingOptions. | No users. |
| ExtensionDataItem | Item | MLC | One "extension" data model element. An "extension" data model element is a data model element whose name does not begin with "cmi." or "adl.". | No users. |
| InteractionItem | Item | MLC | Information about one interaction within one activity of one attempt. | No users. |
| RubricItem | Item | MLC | Information about a rubric that is attached to an interaction. | No users. |
| EvaluationCommentItem | Item | MLC | A comment from an evaluator (i.e. an instructor grading a learner assignment) related to one InteractionItem. Only used for e-learning packages in Class Server format. | No users. |
| CorrectResponseItem | Item | MLC | Information about one SCORM correct response string for one InteractionItem. The CorrectResponseItem table contains one row in this table per correct response per interaction. | No users. |
| InteractionObjectiveItem | Item | MLC | Relates one InteractionItem with one AttemptObjectiveItem. | No users. |
The SLK Queries and Query Sets section gave a brief example (the "Users" query) of how the LearningStore schema of a SLK database can be upgraded to add a security rule. Here, we explain in more detail how to add and update views and security rules.